﻿<%@ WebHandler Language="C#" Class="StatusOfProductionDashBoard" %>

using System;
using System.IO;
using System.Web;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using Newtonsoft.Json.Converters;
using System.Collections.Generic;
using System.Data;
using System.Text;
using ReportBusiness;
using System.Net;

/*
 * Description: 生产完成情况看板
 * Copyright (c) : 通力凯顿（北京）系统集成有限公司
 * Writer:zhunan
 * Date:2022.8.29
 */

public class StatusOfProductionDashBoard : IHttpHandler
{
    private uMESReportOracleDAL dal = new uMESReportOracleDAL();
    public bool IsReusable { get { return false; } }
    public void ProcessRequest(HttpContext context)
    {
        string requestjson = "";
        using (StreamReader reader = new StreamReader(context.Request.InputStream)) { requestjson = reader.ReadToEnd(); }
        JObject jObject = JsonConvert.DeserializeObject(requestjson) as JObject;
        if (jObject["type"] != null)
        {
            try
            {
                Result r = new Result();
                context.Response.ContentType = "text/json";
                string type = jObject["type"].ToString();
                switch (type)
                {
                    case "getStepPlanChartData":
                        r = getStepPlanChartData(jObject);//工序排产
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getShipPlanChartData":
                        r = getShipPlanChartData(jObject);//转工排产
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getProblemChartData":
                        r = getProblemChartData(jObject);//问题上报
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getWorkcenterCoop":
                        r = getWorkcenterCoop(jObject);//工区协作
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    default:
                        break;
                }
            }
            catch (Exception e)
            {
                Result r = new Result();
                r.data = null;
                r.meta = new Result.Meta(500, e.Message);
                r.total = 0;
                context.Response.ContentType = "text/json";
                context.Response.Write(JsonConvert.SerializeObject(r));
            }
        }
    }

    //工序排产
    Result getStepPlanChartData(JObject jObject)
    {
        DataTable dtWorkcenter = getWorkcenter(jObject);
        string sql = @"
SELECT cci.containerno,cci.containername,cci.productname,cci.productdescription,cci.workcentername,csd.plannedfinishdate,
       cci.workflowstepname currentspec,cci.stepplancreatedate,
       ws.workflowstepname,ws.sequence,NVL(NVL(NVL(t.checkDate,cfc.createdate),csr.recivedate),cm.indate) checkDate,
       e.fullname stepplancreateemployeename,
       CASE WHEN NVL(NVL(NVL(t.checkDate,cfc.createdate),csr.recivedate),cm.indate) IS NOT NULL AND 
                 TRUNC(NVL(NVL(NVL(t.checkDate,cfc.createdate),csr.recivedate),cm.indate)) - TRUNC(csd.plannedfinishdate) > 0 THEN 1 
            WHEN NVL(NVL(NVL(t.checkDate,cfc.createdate),csr.recivedate),cm.indate) IS NULL AND TRUNC(SYSDATE) - TRUNC(csd.plannedfinishdate) > 0 THEN 1
            ELSE 0 
       END status,
       CASE WHEN NVL(NVL(NVL(t.checkDate,cfc.createdate),csr.recivedate),cm.indate) IS NOT NULL THEN 1 ELSE 0 END isFinish,
       CASE WHEN NVL(NVL(NVL(t.checkDate,cfc.createdate),csr.recivedate),cm.indate) IS NOT NULL 
                 AND TRUNC(NVL(NVL(NVL(t.checkDate,cfc.createdate),csr.recivedate),cm.indate)) - TRUNC(csd.plannedfinishdate) > 0 THEN 1 
            ELSE 0 
       END isDelayFinish
FROM containercurrentinfo cci
LEFT JOIN factory f ON f.factoryid = cci.originalfactoryid
INNER JOIN containerstepplan csd ON csd.containerid = cci.containerid AND csd.plantype = 1 
LEFT JOIN workflowstep ws ON ws.workflowstepid = csd.workflowstepid
LEFT JOIN 
(
    SELECT csr.containerid,csr.workflowstepid,MAX(csr.checkdate) checkDate
    FROM containersteprecord csr
    GROUP BY csr.containerid,csr.workflowstepid 
) t ON t.containerid = cci.containerid AND t.workflowstepid = ws.workflowstepid
LEFT JOIN containerfinishedcheckinfo cfc ON cfc.containerid = csd.containerid AND csd.workflowstepid = cfc.workflowstepid AND cfc.isuse = 0
LEFT JOIN containercommissioning cm ON cm.containerid = cci.containerid AND cm.workflowstepid = ws.workflowstepid AND cm.isuse = 0
LEFT JOIN 
(
    SELECT crrd.workflowstepid,crr.containerid,crr.recivedate,crr.isuse
    FROM containershiprecive crr
    LEFT JOIN containershiprecordstep crrd ON crrd.containershiprecordid = crr.containershiprecordid
    WHERE crr.isuse = 0
) csr ON csr.containerid = cci.containerid AND csr.workflowstepid = ws.workflowstepid AND csr.isuse = 0
LEFT JOIN employee e ON e.employeeid = cci.stepplancreateemployeeid
WHERE cci.containerstatus = 1 AND cci.workflowroute IS NULL 
";
        if (jObject["factoryid"] != null && jObject["factoryid"].ToString() != "")
        {
            sql += string.Format(" AND cci.originalfactoryid = '{0}'", jObject["factoryid"].ToString());
        }

        if (jObject["selectdate"] != null && jObject["selectdate"].ToString() != "")
        {
            JArray arr = JArray.Parse(jObject["selectdate"].ToString());
            if (arr.Count == 2)
            {
                sql += string.Format(" AND csd.plannedfinishdate >= TO_DATE('{0} 00:00:00','yyyy-mm-dd hh24:mi:ss')", arr[0].ToString());
                sql += string.Format(" AND csd.plannedfinishdate <= TO_DATE('{0} 23:59:59','yyyy-mm-dd hh24:mi:ss')", arr[1].ToString());
            }
        }
        DataTable dtSpecPlanInfo = dal.GetDataTable(sql);

        DataTable dtCompute = dtSpecPlanInfo.DefaultView.ToTable(true, "workcentername");
        dtCompute.Columns.Add("specCount"); //排产工序数
        dtCompute.Columns.Add("finishSpecCount");//完工工序数
        dtCompute.Columns.Add("delayfinishedSum");//超期完工数
        dtCompute.Columns.Add("intimeFinishCount");//按时完工数
        dtCompute.Columns.Add("notFinishCount");//未完工序数
        dtCompute.Columns.Add("finishRate");//完成率

        foreach (DataRow dr in dtCompute.Rows)
        {
            string specCountFilter = string.Format(" workcentername = '{0}'", dr["workcentername"]);
            string finishSpecCountFilter = string.Format("workcentername = '{0}' and isFinish = 1", dr["workcentername"]);
            string delayfinishedSumFilter = string.Format("workcentername = '{0}' and isDelayFinish = 1 and isFinish = 1", dr["workcentername"]);
            string notFinishCountFilter = string.Format("workcentername = '{0}' and isFinish = 0", dr["workcentername"]);

            //计算排产工序数
            dr["specCount"] = dtSpecPlanInfo.Compute("Count(workflowstepname)", specCountFilter);
            //计算完工工序数
            dr["finishSpecCount"] = dtSpecPlanInfo.Compute("Count(workflowstepname)", finishSpecCountFilter);
            //计算超期完工数
            dr["delayfinishedSum"] = dtSpecPlanInfo.Compute("Count(workflowstepname)", delayfinishedSumFilter);
            //计算按时完工数  计算按时完工数=完工工序数-超期完工数
            dr["intimeFinishCount"] = Convert.ToInt32(dr["finishSpecCount"]) - Convert.ToInt32(dr["delayfinishedSum"]);
            //计算未完工序数
            dr["notFinishCount"] = dtSpecPlanInfo.Compute("Count(workflowstepname)", notFinishCountFilter);

            //计算完成率 完成率 = 完工工序数/排产工序数
            if (dr["specCount"].ToString() == "0")
            {
                dr["finishRate"] = 0;
            }
            else
            {
                decimal finishRate = (Convert.ToDecimal(dr["finishSpecCount"]) / Convert.ToDecimal(dr["specCount"])) * 100;// 完成率 = 完工工序数 / 工序总数
                dr["finishRate"] = finishRate.ToString("0.00");
            }
        }

        dtWorkcenter.Columns.Add("specCount"); //排产工序数
        dtWorkcenter.Columns.Add("finishSpecCount");//完工工序数
        dtWorkcenter.Columns.Add("delayfinishedSum");//超期完工数
        dtWorkcenter.Columns.Add("intimeFinishCount");//按时完工数
        dtWorkcenter.Columns.Add("notFinishCount");//未完工序数
        dtWorkcenter.Columns.Add("finishRate");//完成率

        foreach (DataRow datarow in dtWorkcenter.Rows)
        {
            string filter = string.Format("workcentername = '{0}'", datarow["workcentername"]);

            DataRow[] drList = dtCompute.Select(filter);
            if (drList.Length > 0)
            {
                datarow["specCount"] = drList[0]["specCount"];
                datarow["finishSpecCount"] = drList[0]["finishSpecCount"];
                datarow["delayfinishedSum"] = drList[0]["delayfinishedSum"];
                datarow["intimeFinishCount"] = drList[0]["intimeFinishCount"];
                datarow["notFinishCount"] = drList[0]["notFinishCount"];
                datarow["finishRate"] = drList[0]["finishRate"];
            }
            else
            {
                datarow["specCount"] = 0;
                datarow["finishSpecCount"] = 0;
                datarow["delayfinishedSum"] = 0;
                datarow["intimeFinishCount"] = 0;
                datarow["notFinishCount"] = 0;
                datarow["finishRate"] = 0;
            }
        }

        foreach (DataColumn col in dtWorkcenter.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }

        Result r = new Result();
        r.data = dtWorkcenter;
        r.total = Convert.ToInt32(dtWorkcenter.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //转工排产
    Result getShipPlanChartData(JObject jObject)
    {
        DataTable dtWorkcenter = getWorkcenter(jObject);
        string sql = @"
SELECT f.factoryname originalfactoryname,cci.productname,cci.togetherflag,cci.containerno,cci.containername,cci.fromworkcentername workcentername,cci.qty,
       ws.workflowstepname planedspecname,f2.factoryname tofactoryname,
       csp.plannedstartdate,t.senddate,
       csp.plannedfinishdate,t.completedate,
       cci.workflowstepname currentworkflowstepname,
       CASE WHEN t.senddate IS NOT NULL THEN TRUNC(t.senddate) - TRUNC(csp.plannedstartdate)
            ELSE TRUNC(SYSDATE) - TRUNC(csp.plannedstartdate) 
       END senddelaydays,
       CASE WHEN csp.plannedfinishdate IS NOT NULL THEN 
                 CASE WHEN t.completedate IS NOT NULL THEN TRUNC(t.completedate) - TRUNC(csp.plannedfinishdate)
                      ELSE TRUNC(SYSDATE) - TRUNC(csp.plannedfinishdate) END
            ELSE 0 
       END finishdelaydays
FROM containerstepplan csp
LEFT JOIN containercurrentinfo cci ON cci.containerid = csp.containerid
LEFT JOIN workflowstep ws ON ws.workflowstepid = csp.workflowstepid
LEFT JOIN factory f ON f.factoryid = cci.originalfactoryid
LEFT JOIN workflowstep wss ON wss.workflowstepid = csp.workflowstepid
LEFT JOIN specbase sb ON sb.specbaseid = wss.specbaseid
LEFT JOIN spec s ON s.specid = NVL(sb.revofrcdid,wss.specid)
LEFT JOIN factory f2 ON f2.factoryid = s.factoryid
LEFT JOIN (
             SELECT csd.containerid,csdd.workflowstepid,csd.senddate,csr.completedate
             FROM containershiprecord csd
             LEFT JOIN containercurrentinfo cci ON cci.containerid = csd.containerid
             LEFT JOIN containershiprecive csr ON csr.containershiprecordid = csd.containershiprecordid
             LEFT JOIN containershiprecordstep csdd ON csdd.containershiprecordid = csd.containershiprecordid
             WHERE csd.isuse = 0 AND cci.containerstatus = 1
          ) t ON t.containerid = csp.containerid AND t.workflowstepid = csp.workflowstepid
WHERE csp.plantype = 2 AND csp.plannedstartdate IS NOT NULL AND cci.containerstatus = 1 AND csp.islocked IS NOT NULL AND cci.workflowroute IS NULL 
";
        if (jObject["factoryid"] != null && jObject["factoryid"].ToString() != "")
        {
            sql += string.Format(" AND cci.originalfactoryid = '{0}'", jObject["factoryid"].ToString());
        }

        if (jObject["selectdate"] != null && jObject["selectdate"].ToString() != "")
        {
            JArray arr = JArray.Parse(jObject["selectdate"].ToString());
            if (arr.Count == 2)
            {
                sql += string.Format(" AND csp.plannedstartdate >= TO_DATE('{0} 00:00:00','yyyy-mm-dd hh24:mi:ss')", arr[0].ToString());
                sql += string.Format(" AND csp.plannedstartdate <= TO_DATE('{0} 23:59:59','yyyy-mm-dd hh24:mi:ss')", arr[1].ToString());
            }
        }

        DataTable dtSpecPlanInfo = dal.GetDataTable(sql);

        DataTable dtCompute = dtSpecPlanInfo.DefaultView.ToTable(true, "workcentername");

        dtCompute.Columns.Add("shipSpecCount");//计划发出项
        dtCompute.Columns.Add("haveShipedCount");//实际发出项
        dtCompute.Columns.Add("delayShipedCount");//超期发出项
        dtCompute.Columns.Add("intimeShipedCount");//按计划发出项


        foreach (DataRow dr in dtCompute.Rows)
        {
            string shipSpecCountFilter = string.Format("workcentername = '{0}'", dr["workcentername"]);
            string haveShipedCountFilter = string.Format("workcentername = '{0}' AND senddate IS NOT NULL ", dr["workcentername"]);
            string delayShipedCountFilter = string.Format("workcentername = '{0}' AND senddate IS NOT NULL AND senddelaydays > 0 ", dr["workcentername"]);
            //string intimeShipedCount

            //计算计划发出项
            dr["shipSpecCount"] = dtSpecPlanInfo.Compute("Count(planedspecname)", shipSpecCountFilter);
            //计算实际发出项
            dr["haveShipedCount"] = dtSpecPlanInfo.Compute("Count(planedspecname)", haveShipedCountFilter);
            //计算超期发出项
            dr["delayShipedCount"] = dtSpecPlanInfo.Compute("Count(planedspecname)", delayShipedCountFilter);
            //计算按计划发出项
            dr["intimeShipedCount"] = Convert.ToInt32(dr["haveShipedCount"]) - Convert.ToInt32(dr["delayShipedCount"]);
        }

        dtWorkcenter.Columns.Add("shipSpecCount");//计划发出项
        dtWorkcenter.Columns.Add("haveShipedCount");//实际发出项
        dtWorkcenter.Columns.Add("delayShipedCount");//超期发出项
        dtWorkcenter.Columns.Add("intimeShipedCount");//按计划发出项

        foreach (DataRow datarow in dtWorkcenter.Rows)
        {
            string filter = string.Format("workcentername = '{0}'", datarow["workcentername"]);

            DataRow[] drList = dtCompute.Select(filter);
            if (drList.Length > 0)
            {
                datarow["shipSpecCount"] = drList[0]["shipSpecCount"];
                datarow["haveShipedCount"] = drList[0]["haveShipedCount"];
                datarow["delayShipedCount"] = drList[0]["delayShipedCount"];
                datarow["intimeShipedCount"] = drList[0]["intimeShipedCount"];
            }
            else
            {
                datarow["shipSpecCount"] = 0;
                datarow["haveShipedCount"] = 0;
                datarow["delayShipedCount"] = 0;
                datarow["intimeShipedCount"] = 0;
            }
        }

        sql = @"
SELECT cci.containername,ws.workflowstepname,cci.fromworkcentername workcentername
FROM containershiprecord csd 
LEFT JOIN containercurrentinfo cci ON cci.containerid = csd.containerid
LEFT JOIN containerstepplan csp ON csp.containerid = cci.containerid AND csp.plantype = 2
LEFT JOIN containershiprecordstep csds ON csds.containershiprecordid = csd.containershiprecordid
LEFT JOIN workflowstep ws ON ws.workflowstepid = csds.workflowstepid
LEFT JOIN factory f ON f.factoryid = cci.originalfactoryid
WHERE csds.workflowstepid <> csp.workflowstepid AND cci.containerstatus = 1 AND cci.isinstore = 0 AND csp.islocked IS NOT NULL AND cci.workflowroute IS NULL 
";
        if (jObject["factoryid"] != null && jObject["factoryid"].ToString() != "")
        {
            sql += string.Format(" AND cci.originalfactoryid = '{0}'", jObject["factoryid"].ToString());
        }

        if (jObject["selectdate"] != null && jObject["selectdate"].ToString() != "")
        {
            JArray arr = JArray.Parse(jObject["selectdate"].ToString());
            if (arr.Count == 2)
            {
                sql += string.Format(" AND csd.senddate >= TO_DATE('{0} 00:00:00','yyyy-mm-dd hh24:mi:ss')", arr[0].ToString());
                sql += string.Format(" AND csd.senddate <= TO_DATE('{0} 23:59:59','yyyy-mm-dd hh24:mi:ss')", arr[1].ToString());

                sql += string.Format(" AND csp.plannedstartdate >= TO_DATE('{0} 00:00:00','yyyy-mm-dd hh24:mi:ss')", arr[0].ToString());
                sql += string.Format(" AND csp.plannedstartdate <= TO_DATE('{0} 23:59:59','yyyy-mm-dd hh24:mi:ss')", arr[1].ToString());
            }
        }

        DataTable dtShipSpecInfo = dal.GetDataTable(sql);

        dtWorkcenter.Columns.Add("notPlanSpecCount");

        foreach (DataRow _datarow in dtWorkcenter.Rows)
        {
            string filter = string.Format("workcentername = '{0}'", _datarow["workcentername"]);
            _datarow["notPlanSpecCount"] = dtShipSpecInfo.Compute("Count(workflowstepname)", filter);
        }

        foreach (DataColumn col in dtWorkcenter.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }

        Result r = new Result();
        r.data = dtWorkcenter;
        r.total = Convert.ToInt32(dtWorkcenter.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    Result getProblemChartData(JObject jObject)
    {
        DataTable dtWorkcenter = getWorkcenter(jObject);

        string sql = @"
SELECT w.workcentername,DECODE(up.ufcstatus,0,'已关闭',1,'未处理',2,'处理中',3,'处理完') ufcstatus,up.ufcchulidate,up.ufcwanchengdate,
       CASE WHEN (up.ufcstatus = 1 OR up.ufcstatus = 2) then TRUNC((SYSDATE-up.ufcchulidate),2) ELSE 0 END delaysdays
FROM ufcproblem up
LEFT JOIN workcenter w ON w.workcenterid = up.workcenterid
";
        if (jObject["factoryid"] != null && jObject["factoryid"].ToString() != "")
        {
            sql += string.Format(" WHERE up.factoryid = '{0}'", jObject["factoryid"].ToString());
        }

        if (jObject["selectdate"] != null && jObject["selectdate"].ToString() != "")
        {
            JArray arr = JArray.Parse(jObject["selectdate"].ToString());
            if (arr.Count == 2)
            {
                sql += string.Format(" AND up.ufccreatedate >= TO_DATE('{0} 00:00:00','yyyy-mm-dd hh24:mi:ss')", arr[0].ToString());
                sql += string.Format(" AND up.ufccreatedate <= TO_DATE('{0} 23:59:59','yyyy-mm-dd hh24:mi:ss')", arr[1].ToString());
            }
        }

        DataTable dtProblemInfo = dal.GetDataTable(sql);

        dtWorkcenter.Columns.Add("totalCount");//总项数
        dtWorkcenter.Columns.Add("finishedCount");//处理完
        dtWorkcenter.Columns.Add("notdealedCount");//处理中/未处理
        dtWorkcenter.Columns.Add("closedCount");//已关闭
        dtWorkcenter.Columns.Add("delayCount");//超期项数
        dtWorkcenter.Columns.Add("replyRate");//回复完成率

        foreach (DataRow dr in dtWorkcenter.Rows)
        {
            string totalCountFilter = string.Format("workcentername = '{0}'", dr["workcentername"]);
            string finishedCountFilter = string.Format("workcentername = '{0}' AND ufcstatus = '处理完'", dr["workcentername"]);
            string notdealedCountFilter = string.Format("workcentername = '{0}' AND (ufcstatus = '处理中' OR ufcstatus = '未处理')", dr["workcentername"]);
            string closedCountFilter = string.Format("workcentername = '{0}' AND ufcstatus = '已关闭'", dr["workcentername"]);
            string delayCountFilter = string.Format("workcentername = '{0}' AND delaysdays > 0", dr["workcentername"]);

            //计算总项数
            dr["totalCount"] = dtProblemInfo.Compute("Count(workcentername)", totalCountFilter);
            //计算处理完
            dr["finishedCount"] = dtProblemInfo.Compute("Count(workcentername)", finishedCountFilter);
            //计算处理中/未处理
            dr["notdealedCount"] = dtProblemInfo.Compute("Count(workcentername)", notdealedCountFilter);
            //计算已关闭
            dr["closedCount"] = dtProblemInfo.Compute("Count(workcentername)", closedCountFilter);
            //计算超期项数
            dr["delayCount"] = dtProblemInfo.Compute("Count(workcentername)", delayCountFilter);

            //计算回复完成率
            if (dr["totalCount"].ToString() != "0")
            {
                // 回复完成率 = (处理完+已关闭)/总项数
                decimal replyRate = ((Convert.ToDecimal(dr["finishedCount"]) + Convert.ToDecimal(dr["closedCount"])) / Convert.ToDecimal(dr["totalCount"])) * 100;
                dr["replyRate"] = replyRate.ToString("0.00");
            }
            else
            {
                dr["replyRate"] = 0;
            }
        }

        foreach (DataColumn col in dtWorkcenter.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }

        Result r = new Result();
        r.data = dtWorkcenter;
        r.total = Convert.ToInt32(dtWorkcenter.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    Result getWorkcenterCoop(JObject jObject)
    {
        string sql = @"
SELECT w.workcentername,w.workcenterid
FROM factory f
LEFT JOIN workcenter w ON w.factoryid = f.factoryid
WHERE f.factoryid='{0}' AND w.wctype IS NULL AND w.workcentername <> '孙刚工作站' AND w.notes IS NULL 
";
        sql = string.Format(sql, jObject["factoryid"].ToString());

        DataTable dtWorkcenter = dal.GetDataTable(sql);

        sql = @"
SELECT cci.mfgordername,cci.productname,cci.containername,w.workcentername applyWorkcenter,ws.workflowstepname,c.originalqty,
       w2.workcentername toWorkcener,cwd.createdate,cwd.recvdate,cwd.completedate needcompletedate,csd.checkdate,cci.workflowstepname currentworkflowstepname,
       DECODE(csd.checkdate,NULL,0,1) isfinished
FROM containerworkcenterdispatch cwd
LEFT JOIN containercurrentinfo cci ON cci.containerid = cwd.containerid
LEFT JOIN container c ON c.containerid = cwd.containerid
LEFT JOIN workcenter w ON w.workcenterid = cwd.workcenterid
LEFT JOIN workcenter w2 ON w2.workcenterid = cwd.toworkcenterid
LEFT JOIN workflowstep ws ON ws.workflowstepid = cwd.workflowstepid
LEFT JOIN containersteprecord csd ON csd.containerid = cwd.containerid AND csd.workflowstepid = cwd.workflowstepid
WHERE cwd.isuse = 0 AND cci.containerid IS NOT NULL AND cci.containerstatus = 1 AND cci.workflowroute IS NULL
";
        if (jObject["factoryid"] != null && jObject["factoryid"].ToString() != "")
        {
            sql += string.Format(" AND cci.originalfactoryid = '{0}'", jObject["factoryid"].ToString());
        }

        if (jObject["selectdate"] != null && jObject["selectdate"].ToString() != "")
        {
            JArray arr = JArray.Parse(jObject["selectdate"].ToString());
            if (arr.Count == 2)
            {
                sql += string.Format(" AND cwd.createdate >= TO_DATE('{0} 00:00:00','yyyy-mm-dd hh24:mi:ss')", arr[0].ToString());
                sql += string.Format(" AND cwd.createdate <= TO_DATE('{0} 23:59:59','yyyy-mm-dd hh24:mi:ss')", arr[1].ToString());
            }
        }

        DataTable dtWorkcenterCoop = dal.GetDataTable(sql);

        dtWorkcenter.Columns.Add("applyCount");//申请项数
        dtWorkcenter.Columns.Add("aceptCount");//接收项数
        dtWorkcenter.Columns.Add("completeCount");//完工项数
        dtWorkcenter.Columns.Add("notCompleteCount");//未完工项数
        dtWorkcenter.Columns.Add("completeRate");//完成率

        foreach (DataRow dr in dtWorkcenter.Rows)
        {
            string applyCountFiter = string.Format("applyWorkcenter = '{0}'", dr["workcentername"]);
            string aceptCountFilter = string.Format("toWorkcener='{0}' AND recvdate IS NOT NULL ", dr["workcentername"]);
            string completeCountFilter = string.Format("toWorkcener='{0}' AND recvdate IS NOT NULL AND isfinished = 1", dr["workcentername"]);

            //计算申请项数
            dr["applyCount"] = dtWorkcenterCoop.Compute("Count(workflowstepname)", applyCountFiter);
            //计算接收项数
            dr["aceptCount"] = dtWorkcenterCoop.Compute("Count(workflowstepname)", aceptCountFilter);
            //计算完工项数
            dr["completeCount"] = dtWorkcenterCoop.Compute("Count(workflowstepname)", completeCountFilter);
            //未完工项数 = 接收项数 - 完工项数
            dr["notCompleteCount"] = Convert.ToInt32(dr["aceptCount"]) - Convert.ToInt32(dr["completeCount"]);

            //计算完成率
            if (dr["aceptCount"].ToString() == "0")
            {
                dr["completeRate"] = 0;
            }
            else
            {
                decimal completeRate = (Convert.ToDecimal(dr["completeCount"]) / Convert.ToDecimal(dr["aceptCount"])) * 100;
                dr["completeRate"] = completeRate.ToString("0.00");
            }

        }

        foreach (DataColumn col in dtWorkcenter.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }

        Result r = new Result();
        r.data = dtWorkcenter;
        r.total = Convert.ToInt32(dtWorkcenter.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    DataTable getWorkcenter(JObject jObject)
    {
        string sql = @"
SELECT w.workcentername,w.workcenterid
FROM factory f
LEFT JOIN workcenter w ON w.factoryid = f.factoryid
WHERE f.factoryid='{0}' AND w.wctype IS NULL AND w.workcentername <> '孙刚工作站' AND w.notes IS NULL 
";
        sql = string.Format(sql, jObject["factoryid"].ToString());

        return dal.GetDataTable(sql);
    }
}